<?php

include_once 'DBCon.php';
include_once 'Utilities.php';
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Seat
 *
 * @author user
 */
class Seat {

    private $shid;
    private $seatno;
    private $status;
    private $bookeddate;
    private $referenceno;
    private $coach;
    private $emptyseat;
    private $con;
    private $getemptyseat = 'SELECT s.* FROM (SELECT count(s.seatid) as emptySeat 
        FROM seat s WHERE s.seatid not in (SELECT bs.seatid FROM bookedseat bs WHERE bookedDate = ? AND shid = ? AND status != "CCL")) s;';
    private $bookseat = 'INSERT INTO bookedseat VALUES (?,?,?,?,?)';
    private $getcoachmap = 'SELECT seatid FROM seat WHERE coach = ?;';
    private $getbookedeseat = 'SELECT bookedseat.seatid FROM seat LEFT JOIN bookedseat 
        ON seat.seatid = bookedseat.seatid WHERE bookedDate = ? AND shid = ? AND coach = ? AND status NOT IN("CCL","LCK");';
    private $getcoach = 'SELECT distinct coach FROM seat;';
    private $getseatno = 'SELECT seatNo FROM seat WHERE seatid = ?';
    private $getlockedseat = 'SELECT seatid FROM bookedseat WHERE status = "LCK" AND shid = ? AND bookedDate = ?;';
    private $cancelbookedseat = 'UPDATE bookedseat SET status = "CCL" WHERE referenceNo = ?;';
    private $amendbookedseat = 'UPDATE bookedseat SET shid = ?, seatid = ?, status = "AMD", bookedDate = ? WHERE referenceNo = ?;';

    function __construct() {
        $this->con = DBCon::connectDB();
    }

    public function createSeat($shid, $seatno, $status, $bookeddate, $referenceno) {
        $this->shid = $shid;
        $this->seatno = $seatno;
        $this->status = $status;
        $this->bookeddate = $bookeddate;
        $this->referenceno = $referenceno;
    }

    public function getNoOfEmptySeat($date, $shid) {
        $pstmtselect = $this->con->prepare($this->getemptyseat);
        $pstmtselect->bind_param('ss', $date, $shid);
        $pstmtselect->bind_result($this->emptyseat);
        $pstmtselect->execute();
        $pstmtselect->fetch();
        return $this->emptyseat;
    }

    public function getBookedSeat($date, $shid, $coach) {
        $booked = null;
        $pstmtselectseat = $this->con->prepare($this->getbookedeseat);
        $pstmtselectseat->bind_param('sis', $date, $shid, $coach);
        $pstmtselectseat->execute();
        $pstmtselectseat->bind_result($booked);
        while ($pstmtselectseat->fetch()) {
            $data[] = $booked;
        }
        if (isset($data)) {
            return $data;
        } else {
            return NULL;
        }
    }

    public function getCoachmap($coach) {
        $map = NULL;
        $pstmtselectmap = $this->con->prepare($this->getcoachmap);
        $pstmtselectmap->bind_param('s', $coach);
        $pstmtselectmap->execute();
        $pstmtselectmap->bind_result($map);
        while ($pstmtselectmap->fetch()) {
            $data[] = $map;
        }
        return $data;
    }

    public function getCoach() {
        $coach = '';
        $pstmtselect = $this->con->prepare($this->getcoach);
        $pstmtselect->execute();
        $pstmtselect->bind_result($coach);
        while ($pstmtselect->fetch()) {
            $data[] = $coach;
        }
        return $data;
    }

    public function getSeatNo($seatid) {
        $pstmtselect = $this->con->prepare($this->getseatno);
        $pstmtselect->bind_param('s', $seatid);
        $pstmtselect->execute();
        $pstmtselect->bind_result($this->seatno);
        $pstmtselect->fetch();
        return $this->seatno;
    }

    public function getLockedSeat($shid, $date) {
        $locked = '';
        $pstmtselect = $this->con->prepare($this->getlockedseat);
        $pstmtselect->bind_param('is', $shid, $date);
        $pstmtselect->bind_result($locked);
        $pstmtselect->execute();
        while ($pstmtselect->fetch()) {
            $data[] = $locked;
        }
        if (isset($data)) {
            return $data;
        } else {
            return NULL;
        }
    }

    public function bookSeat($shid, $seatno, $status, $bookeddate, $referenceno) {
        $pstmtinsert = $this->con->prepare($this->bookseat);
        $pstmtinsert->bind_param('isssi', $shid, $seatno, $status, $bookeddate, $referenceno);
        $pstmtinsert->execute();
        if ($pstmtinsert->affected_rows > 0) {
            return true;
        } else {
            return false;
        }
    }

    public function cancelBookedSeat($reference) {
        $pstmtupdate = $this->con->prepare($this->cancelbookedseat);
        $pstmtupdate->bind_param('s', $reference);
        $pstmtupdate->execute();
        if ($pstmtupdate->affected_rows > 0) {
            return true;
        }
        return false;
    }

    public function amendBookedSeat($shid, $seatid, $date, $referenceno) {
        $pstmtamend = $this->con->prepare($this->amendbookedseat);
        $pstmtamend->bind_param('iisi', $shid, $seatid, $date, $referenceno);
        $pstmtamend->execute();
        if ($pstmtamend->affected_rows > 0) {
            return true;
        }
        return false;
    }

}

?>
